package net.stuffrepos.bricknetcenter.database;

import java.sql.SQLException;
import net.stuffrepos.userdesktopdatabase.Database;
import net.stuffrepos.userdesktopdatabase.Migration;
import net.stuffrepos.util.config.UserConfig;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author Eduardo H. Bogoni <eduardobogoni@gmail.com>
 */
public class DatabaseManager {

    private static final UserConfig USER_CONFIG = new UserConfig("bricknetcenter");
    private static final Migration[] MIGRATIONS = new Migration[]{
        new Migration() {

            @Override
            public String[] up() {
                return new String[]{
                            "create table inventories ("
                            + "id varchar(32) not null,"
                            + "name varchar(255) not null,"
                            + "comments varchar(32767),"
                            + "createdOn timestamp not null,"
                            + "primary key (id)"
                            + ")"
                        };
            }

            @Override
            public String[] down() {
                return new String[]{
                            "drop table inventories"
                        };
            }
        },
        new Migration() {

            @Override
            public String[] up() {

                return new String[]{
                            "create table transfer_objects ("
                            + "id varchar(32) not null, "
                            + "code varchar(255), "
                            + "comments varchar(32767), "
                            + "createdOn timestamp, "
                            + "modifiedOn timestamp, "
                            + "quantity integer, "
                            + "state varchar(255), "
                            + "type varchar(255), "
                            + "transfer_id varchar(32) null, "
                            + "primary key (id)"
                            + ")",
                            "create table transfers ("
                            + "id varchar(32) not null, "
                            + "comments varchar(32767), "
                            + "createdOn timestamp not null, "
                            + "date date, "
                            + "modifiedOn timestamp not null, "
                            + "inventory_id varchar(32) null, "
                            + "primary key (id)"
                            + ")",
                            "alter table transfer_objects "
                            + "add constraint fk_transferobject_transfer "
                            + "foreign key (transfer_id) references transfers",
                            "alter table transfers "
                            + "add constraint fk_transfer_inventory "
                            + "foreign key (inventory_id) references inventories"
                        };
            }

            @Override
            public String[] down() {
                return new String[]{
                            "drop constraint fk_transfer_inventory",
                            "drop constraint fk_transferobject_transfer",
                            "drop table transfers",
                            "drop table transfer_objects"
                        };
            }
        },
        new Migration() {

            @Override
            public String[] up() {
                return new String[]{
                            "alter table transfer_objects add column color varchar(255)"
                        };
            }

            @Override
            public String[] down() {
                return new String[]{
                            "alter table transfer_objects drop column color"
                        };
            }
        },
        new Migration() {

            @Override
            public String[] up() {
                return new String[]{
                            "create table http_requests ("
                            + "id varchar(32) not null, "
                            + "parserClass varchar(4096), "
                            + "urlSpecification varchar(4096), "
                            + "refererSpecification varchar(4096), "
                            + "priority integer not null, "
                            + "status varchar(255) not null, "
                            + "createdOn timestamp, "
                            + "modifiedOn timestamp, "
                            + "primary key (id)"
                            + ")",
                            "create table informations ("
                            + "id varchar(32) not null, "
                            + "type varchar(255), "
                            + "value varchar(32767), "
                            + "parent_id varchar(32), "
                            + "primary key (id)"
                            + ")",
                            "create table blob_informations ("
                            + "content longvarbinary not null, "
                            + "mimeType varchar(255) not null, "
                            + "id varchar(32) not null, "
                            + "primary key (id)"
                            + ")",
                            "alter table informations "
                            + "add constraint FK357830C7AC050A35 "
                            + "foreign key (parent_id) "
                            + "references informations",
                            "alter table blob_informations "
                            + "add constraint FK2FB27229309E6680 "
                            + "foreign key (id) "
                            + "references informations"
                        };
            }

            @Override
            public String[] down() {
                return new String[]{
                            "drop table http_requests",
                            "drop table blob_informations",
                            "drop table informations",

                        };
            }
        },
        new Migration() {
            @Override
            public String[] up() {
                return new String[]{"create table http_request_post_parameters ("
                            + "id varchar(32) not null, "
                            + "name varchar(4096), "
                            + "value varchar(4096), "
                            + "http_request_id varchar(32) not null, "
                            + "primary key (id)"
                            + ")",
                            "alter table http_request_post_parameters "
                            + "add constraint FK77BF1A82BD53A602 "
                            + "foreign key (http_request_id) "
                            + "references http_requests",
                            "alter table http_requests"
                            + " add column method varchar(255)"
                        };
            }

            @Override
            public String[] down() {
                return new String[]{
                            "drop table http_request_post_parameters",
                            "alter table http_requests"
                            + " drop column method"
                        };
            }
        },
        new Migration() {
            @Override
            public String[] up() {
                return new String[]{"create table http_request_parser_arguments ("
                            + "id varchar(32) not null, "
                            + "call_position integer, "
                            + "value varchar(4096), "
                            + "valueType varchar(255), "
                            + "http_request_id varchar(32) not null, "
                            + "primary key (id)"
                            + ")"};
            }

            @Override
            public String[] down() {
                return new String[]{"drop table http_request_parser_arguments if exists"};
            }
        }
    };
    private static final String DEFAULT_DATABASE_ID = "bricknetcenter";
    private static String databaseId = DEFAULT_DATABASE_ID;
    private static DatabaseManager instance = new DatabaseManager();
    private Database database;
    private Log log = LogFactory.getLog(
            DatabaseManager.class);
    private HibernateUtil hibernateUtil;
    private static boolean clearDatabase = false;

    private DatabaseManager() {
        //setDatabase(null);
    }

    public HibernateUtil getHibernateUtil() {
        return hibernateUtil;
    }

    public static DatabaseManager getInstance() {
        return instance;
    }

    public void requestDatabase(int version) {
        try {
            log.trace("Version: " + version);
            setDatabase(null, clearDatabase, version);
            this.database.open(version);
        } catch (SQLException ex) {
            log.fatal("Exception at opening database", ex);
            System.exit(0);
        }
    }

    public void setDatabase(String database) {
        setDatabase(database, false);
    }

    public void setDatabase(String database, boolean clearDatabase) {
        setDatabase(database, clearDatabase, Database.LAST_VERSION);
    }

    public void setDatabase(String database, boolean clearDatabase, int version) {
        closeDatabase();

        try {
            this.database = new Database(USER_CONFIG, MIGRATIONS, database);
            if (clearDatabase) {
                this.database.clearDatabase();
            }
            this.database.open(version);
            hibernateUtil = new HibernateUtil(this.database.getConnectionUrl());
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    }

    public void closeDatabase() {
        if (hibernateUtil != null) {
            hibernateUtil.close();
        }
        if (this.database != null) {
            this.database.close();
        }
    }

    public static void setDatabaseId(String aDatabaseId) {
        databaseId = aDatabaseId;
    }
    
    public static void setClearDatabase(boolean clearDatabase) {
        DatabaseManager.clearDatabase = clearDatabase;
    }
}
